import time

from PIL.ImageQt import rgb
from pyecharts.charts import Bar, Pie, Line, Radar
import pandas as pd

from jinja2 import Markup, Environment, FileSystemLoader
from pyecharts.faker import Faker
from pyecharts.globals import CurrentConfig, ThemeType

# 初始化pyecharts
CurrentConfig.GLOBAL_ENV = Environment(loader=FileSystemLoader("./templates/pyecharts"))
from pyecharts import options as opts
from corescatter import remove_GB


# 时间转换工具 将Jul-14 转为 2014-07
def format_time(t):
    # 将字符串解析为时间元祖
    t = time.strptime(t, "%b-%Y")
    # 将时间元组格式化为指定的样式
    t = time.strftime("%Y-%m", t)
    return t


# 返回各个公司得分最高的记录的信息的表格 html
def compamy_table() -> str:
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM result_processor")
    data.rename(columns={'company': '公司', 'system_name': '系统名称', 'result': '测试结果(Base)', 'processor': '处理器'},
                inplace=True)
    return data.to_html(index=False, justify='center', na_rep="NULL")


# 返回各个CPU得分最高的记录的信息的表格 html
def processor_table() -> str:
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM result_company")
    data.rename(columns={'company': '公司', 'system_name': '系统名称', 'result': '测试结果(Base)', 'processor': '处理器'},
                inplace=True)
    return data.to_html(index=False, justify='center', na_rep="NULL")


# 不同文件中的数据条数 柱状图 响应html中的数据请求，显示pyechart
def file_item_num_bar() -> Bar:
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM sqlite_sequence")
    # names = ['cpu2006_fprate', 'cpu2006_fpspeed', 'cpu2006_intrate', 'cpu2006_intspeed',
    #          'cpu2017_fprate', 'cpu2017_fpspeed', 'cpu2017_intrate', 'cpu2017_intspeed',
    #          'jbb2015_comp', 'jbb2015_dist', 'jbb2015_multi', 'jvm2008',
    #          'power_ssj2008_multi']
    # nums = [14133, 9355, 15343, 9550, 7587, 5952, 7861, 6078, 167, 127, 361, 11, 777]
    data = data.drop([0, 14])
    names = data['name'].tolist()
    names = [x[3:] for x in names]
    nums = data['seq'].tolist()

    c = (
        Bar()
            .add_xaxis(names)
            .add_yaxis("条数", nums)
            .set_global_opts(
            xaxis_opts=opts.AxisOpts(
                axislabel_opts=opts.LabelOpts(rotate=-30),
                name="类别",
                name_rotate=18,
                name_gap=10
            ),
            yaxis_opts=opts.AxisOpts(
                type_="value",
                name="数据量",
                axistick_opts=opts.AxisTickOpts(is_show=True),
            ),
            title_opts=opts.TitleOpts(title="不同测试的提交数据量对比"),
            legend_opts=opts.LegendOpts(
                # 是否显示图例组件
                is_show=True,
                # 图例位置，配置方法与标题相同
                pos_left='75%',
                pos_top='15%',
                # 图例布局朝向：'horizontal'(默认，横排), 'vertical'(竖排)
                orient='vertical',
                # 对齐方式：`auto`, `left`, `right`
                align='auto',
                # 图例中每项的间隔，默认10
                item_gap=20,
                # 图例宽度和高度，默认为25和14
                # item_width=50,
                # item_height=20
            ),
        )
    )
    return c


# 不同公司提交的数据条数 前10名 饼图 响应html中的数据请求，显示pyechart
def company_submit_num_bar() -> Bar:
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM num_company")
    names = data['name'].tolist()
    nums = data['num'].tolist()

    # print(lables)
    # print(options)
    # x_data = ["直接访问", "邮件营销", "联盟广告", "视频广告", "搜索引擎"]
    # y_data = [335, 310, 274, 235, 400]
    data_pair = [list(z) for z in zip(names, nums)]
    # print(data_pair)
    data_pair.sort(key=lambda x: x[1])
    # print(data_pair)
    data_pair = data_pair[len(data_pair) - 10:]
    # print(data_pair)
    p = (
        Pie()
            .add(
            series_name="公司",
            data_pair=data_pair,
            radius=["40%", "70%"],
            # rosetype="radius",
            # radius="55%",
            # center=["50%", "50%"],
            # label_opts=opts.LabelOpts(is_show=False, position="center"),
        )
            .set_global_opts(
            title_opts=opts.TitleOpts(title="提交量排名前十的公司的数据量占比"),
            # legend_opts=opts.LegendOpts(type_="scroll", pos_left="80%", orient="vertical"),
            legend_opts=opts.LegendOpts(is_show=False),
        )
            .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    )
    return p


# 不同cpu相关的数据条数 前10名 饼图 响应html中的数据请求，显示pyechart
def processor_submit_num_bar() -> Bar:
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM num_processor")
    names = data['name'].tolist()
    nums = data['num'].tolist()

    # print(lables)
    # print(options)
    # x_data = ["直接访问", "邮件营销", "联盟广告", "视频广告", "搜索引擎"]
    # y_data = [335, 310, 274, 235, 400]
    data_pair = [list(z) for z in zip(names, nums)]
    # print(data_pair)
    data_pair.sort(key=lambda x: x[1])
    # print(data_pair)
    data_pair = data_pair[len(data_pair) - 10:]
    # print(data_pair)
    p = (
        Pie()
            .add(
            series_name="处理器",
            data_pair=data_pair,
            radius=["40%", "70%"],
            # rosetype="radius",
            # radius="55%",
            # center=["50%", "50%"],
            # label_opts=opts.LabelOpts(is_show=False, position="center"),
        )
            .set_global_opts(
            title_opts=opts.TitleOpts(title="提交量排名前十的处理器的数据量占比"),
            # legend_opts=opts.LegendOpts(type_="scroll", pos_left="80%", orient="vertical"),
            legend_opts=opts.LegendOpts(is_show=False),
        )
            .set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}"))
    )
    return p


# 不同月份更新的数据条数 柱状图 响应html中的数据请求，显示pyechart
def update_submit_num_bar() -> Bar:
    # 读取数据
    # data = pd.read_csv("data/ods/ods_cpu2006_fprate.csv")
    from dw.dw import DwUtil
    dw = DwUtil()
    data = dw.execute_sql("SELECT * FROM num_updated")
    names = data['name'].tolist()
    nums = data['num'].tolist()

    # c = (
    #     Line()
    #         .add_xaxis(labels)
    #         .add_yaxis("num", options, is_smooth=True)
    #         # .add_yaxis("商家B", Faker.values(), is_smooth=True)
    #         .set_series_opts(
    #             areastyle_opts=opts.AreaStyleOpts(opacity=0.5),
    #             label_opts=opts.LabelOpts(is_show=False),
    #         )
    #         .set_global_opts(
    #             title_opts=opts.TitleOpts(title="Performance Changes over Time"),
    #             xaxis_opts=opts.AxisOpts(
    #                 axistick_opts=opts.AxisTickOpts(is_align_with_label=True),
    #                 is_scale=False,
    #                 boundary_gap=False,
    #             ),
    #             datazoom_opts=opts.DataZoomOpts(),
    #         )
    # )

    c = (
        Bar(init_opts=opts.InitOpts(theme=ThemeType.LIGHT))
            .add_xaxis(names)
            .add_yaxis("条数", nums, itemstyle_opts=opts.ItemStyleOpts(color="cadetblue"))
            .set_global_opts(
            xaxis_opts=opts.AxisOpts(
                # axislabel_opts=opts.LabelOpts(rotate=-30),
                name="数据量",
            ),
            yaxis_opts=opts.AxisOpts(
                type_="value",
                name="时间",
                axistick_opts=opts.AxisTickOpts(is_show=True),
            ),
            title_opts=opts.TitleOpts(title="不同月份提交的测试数据量"),
            datazoom_opts=opts.DataZoomOpts(),
            legend_opts=opts.LegendOpts(
                # 是否显示图例组件
                is_show=True,
                # 图例位置，配置方法与标题相同
                pos_left='75%',
                pos_top='15%',
                # 图例布局朝向：'horizontal'(默认，横排), 'vertical'(竖排)
                orient='vertical',
                # 对齐方式：`auto`, `left`, `right`
                align='auto',
                # 图例中每项的间隔，默认10
                item_gap=20,
                # 图例宽度和高度，默认为25和14
                # item_width=50,
                # item_height=20
            ),
        )
    )
    return c
